﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Xml;
using Common.Utilities.Dispatcher;
using Common.Utilities.DLR;
using Microsoft.Office.Interop.Owc11;
//using OWC11;

namespace Workflows.Components.Reports
{
    public partial class ExcelReportDesigner : Form, IComponentEditor
    {
        private object _OwnerActivityNode;
        private ExcelReportComponent _ExcelReport;
        private ExcelReportInstruction _Instruction;

        /// <summary>
        /// 
        /// </summary>
        public ExcelReportDesigner()
        {
            InitializeComponent();
        }

        private void ExcelReportDesigner_Load(object sender, EventArgs e)
        {
            foreach (DataGridViewRow dr in this.dv_Regions.Rows)
            {
                if (dr.IsNewRow)
                {
                    DataGridViewComboBoxCell cellAction = (DataGridViewComboBoxCell)dr.Cells[5];
                    cellAction.Items.Clear();
                    for (int i = 0; i <= 2; i++)
                    {
                        ExcelDataFillAction action = (ExcelDataFillAction)i;
                        cellAction.Items.Add(action.ToString());
                    }
                }
            }
        }

        public event UpdateComponentSettings UpdateComponentSettingEvent;

        public object OwnerActivityNode
        {
            get { return _OwnerActivityNode; }
            set { _OwnerActivityNode = value; }
        }

        public Type WorkflowComponentType
        {
            get { return typeof(ExcelReportComponent); }
        }

        public void LoadSettings(GlobalVariable[] globalVars, WorkflowComponentBase[] fromComponents,
                                 object activityNode, WorkflowComponentBase wfComponent)
        {
            this._OwnerActivityNode = activityNode;
            this._ExcelReport = (ExcelReportComponent)wfComponent;
            this._Instruction = (ExcelReportInstruction)this._ExcelReport.Instruction;
            this.DisplayExcelReportDoc();
        }

        private void DisplayExcelReportDoc()
        {
            this.txt_ExcelReportName.Text = this._Instruction.ReportName;
            this.txt_ConnStr.Text = this._Instruction.ConnectionString;
            this.txt_ReportFilePath.Text = this._Instruction.ReportFilePath;
            string dataFolderPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "Workflow");
            if (!Directory.Exists(dataFolderPath))
            {
                Directory.CreateDirectory(dataFolderPath);
            }
            string tmpFilePath = Path.Combine(dataFolderPath, "ExcelTemplate_" + this._Instruction.ReportName + ".xml");
            if (File.Exists(tmpFilePath))
            {
                File.Delete(tmpFilePath);
            }
            if (!string.IsNullOrEmpty(this._Instruction.Template))
            {
                XmlDocument xdoc = new XmlDocument();
                xdoc.LoadXml(this._Instruction.Template);
                xdoc.Save(tmpFilePath);
                this.axSpreadsheet1.XMLURL = "file:\\" + tmpFilePath;
            }
            this.dv_Regions.Rows.Clear();
            foreach (DataGridViewRow dr in this.dv_Regions.Rows)
            {
                if (dr.IsNewRow)
                {
                    DataGridViewComboBoxCell cellAction = (DataGridViewComboBoxCell)dr.Cells[5];
                    cellAction.Items.Clear();
                    for (int i = 0; i <= 2; i++)
                    {
                        ExcelDataFillAction action = (ExcelDataFillAction)i;
                        cellAction.Items.Add(action.ToString());
                    }
                }
            }

            if (this._Instruction.DataRegions != null && this._Instruction.DataRegions.Count > 0)
            {
                this._Instruction.DataRegions.Sort();
                foreach (ExcelDataRegion region in this._Instruction.DataRegions)
                {
                    DataGridViewRow dr = new DataGridViewRow();
                    DataGridViewTextBoxCell cellSheet = new DataGridViewTextBoxCell();
                    cellSheet.Value = region.WorksheetName;
                    dr.Cells.Add(cellSheet);
                    DataGridViewTextBoxCell cellRange = new DataGridViewTextBoxCell();
                    cellRange.Value = region.Range;
                    dr.Cells.Add(cellRange);
                    DataGridViewTextBoxCell cellSql = new DataGridViewTextBoxCell();
                    cellSql.Value = region.Sql;
                    dr.Cells.Add(cellSql);
                    DataGridViewTextBoxCell cellKeyPos = new DataGridViewTextBoxCell();
                    cellKeyPos.Value = string.Empty;
                    dr.Cells.Add(cellKeyPos);
                    DataGridViewTextBoxCell cellKeyName = new DataGridViewTextBoxCell();
                    cellKeyName.Value = string.Empty;
                    dr.Cells.Add(cellKeyName);
                    if (region.FillAction == ExcelDataFillAction.FillTableByKey)
                    {
                        cellKeyPos.Value = region.KeyPosition;
                        cellKeyName.Value = region.KeyName;
                    }
                    DataGridViewComboBoxCell cellAction = new DataGridViewComboBoxCell();
                    for (int i = 0; i <= 2; i++)
                    {
                        ExcelDataFillAction action = (ExcelDataFillAction)i;
                        cellAction.Items.Add(action.ToString());
                    }
                    cellAction.Value = region.FillAction.ToString();
                    dr.Cells.Add(cellAction);

                    DataGridViewTextBoxCell cellBindings = new DataGridViewTextBoxCell();
                    string bindings = string.Empty;
                    if (region.ExcelColumnBindings != null && region.ExcelColumnBindings.Count > 0)
                    {
                        foreach (string key in region.ExcelColumnBindings.Keys)
                        {
                            if (bindings.Length > 0)
                            {
                                bindings += ",";
                            }
                            bindings += key + "=" + region.ExcelColumnBindings[key];
                        }
                    }
                    cellBindings.Value = bindings;
                    dr.Cells.Add(cellBindings);
                    this.dv_Regions.Rows.Add(dr);
                }
            }
        }

        public void SaveSettings()
        {
            this._Instruction.ConnectionString = this.txt_ConnStr.Text;
            this._Instruction.ParameterNameValues = new Dictionary<string, string>();
            this._Instruction.ReportName = this.txt_ExcelReportName.Text;
            this._Instruction.WorksheetNames = new List<string>();
            this._Instruction.ReportFilePath = this.txt_ReportFilePath.Text;
            this._Instruction.DataRegions = new List<ExcelDataRegion>();
            foreach (Worksheet wkSheet in this.axSpreadsheet1.Worksheets)
            {
                this._Instruction.WorksheetNames.Add(wkSheet.Name);
            }
            foreach (DataGridViewRow dr in this.dv_Regions.Rows)
            {
                if (!dr.IsNewRow)
                {
                    string sheetName = dr.Cells[0].Value.ToString();
                    string range = dr.Cells[1].Value.ToString();
                    string sql = ((DataGridViewTextBoxCell)dr.Cells[2]).Value.ToString();
                    ExcelDataFillAction action = (ExcelDataFillAction)Enum.Parse(typeof(ExcelDataFillAction), dr.Cells[5].Value.ToString());
                    ExcelDataRegion region = new ExcelDataRegion();
                    region.FillAction = action;
                    region.KeyName = string.Empty;
                    region.KeyPosition = string.Empty;
                    region.Range = range;
                    region.Sql = sql;
                    region.WorksheetName = sheetName;
                    Regex paramRegex = new Regex("(\\S+)\\s*=\\s*@([^\\s()'\"]+)", RegexOptions.IgnoreCase);
                    Match paramMatch = paramRegex.Match(sql);
                    while (paramMatch.Success)
                    {
                        string paramName = paramMatch.Groups[2].Value;
                        if (!this._Instruction.ParameterNameValues.ContainsKey(paramName))
                        {
                            this._Instruction.ParameterNameValues.Add(paramName, string.Empty);
                        }
                        paramMatch = paramMatch.NextMatch();
                    }
                    if (action == ExcelDataFillAction.FillTableByKey)
                    {
                        if (dr.Cells[3].Value != null && dr.Cells[4].Value != null)
                        {
                            string keyPos = dr.Cells[3].Value.ToString();
                            string keyName = dr.Cells[4].Value.ToString();
                            if ((!string.IsNullOrEmpty(keyPos)) && (!string.IsNullOrEmpty(keyName)))
                            {
                                region.KeyPosition = keyPos;
                                region.KeyName = keyName;
                            }
                        }
                    }
                    region.ExcelColumnBindings = null;
                    DataGridViewTextBoxCell cellBindings = (DataGridViewTextBoxCell)dr.Cells[6];
                    if (cellBindings.Value != null)
                    {
                        string bindings = cellBindings.Value.ToString();
                        if (!string.IsNullOrEmpty(bindings))
                        {
                            string[] colBindings = bindings.Split(new char[] { ',' });
                            region.ExcelColumnBindings = new Dictionary<string, string>();
                            foreach (string colBinding in colBindings)
                            {
                                string[] pair = colBinding.Split(new char[] { '=' });
                                if (pair.Length == 2)
                                {
                                    region.ExcelColumnBindings.Add(pair[0], pair[1]);
                                }
                            }
                        }
                    }
                    this._Instruction.DataRegions.Add(region);
                }
            }

            string dataFolderPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "Workflow");
            if (!Directory.Exists(dataFolderPath))
            {
                Directory.CreateDirectory(dataFolderPath);
            }
            string tmpFilePath = Path.Combine(dataFolderPath, "ExcelTemplate_" + this._Instruction.ReportName + ".xml");
            if (File.Exists(tmpFilePath))
            {
                File.Delete(tmpFilePath);
            }
            this.axSpreadsheet1.Export(tmpFilePath, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportXMLSpreadsheet);
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load(tmpFilePath);
            this._Instruction.Template = xDoc.OuterXml;
        }

        public TestResult Test()
        {
            return new TestResult(true, "Success");
        }

        private void cbo_Actions_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.cbo_Actions.SelectedIndex == 2)
            {
                this.lbl_KeyColName.Enabled = true;
                this.lbl_KeyPos.Enabled = true;
                this.txt_KeyColIndex.Enabled = true;
                this.txt_KeyColumnName.Enabled = true;
            }
            else
            {
                this.lbl_KeyColName.Enabled = false;
                this.lbl_KeyPos.Enabled = false;
                this.txt_KeyColIndex.Enabled = false;
                this.txt_KeyColIndex.Text = string.Empty;
                this.txt_KeyColumnName.Enabled = false;
                this.txt_KeyColumnName.Text = string.Empty;
            }
        }

        private void btn_AddRegion_Click(object sender, EventArgs e)
        {
            ExcelDataRegion dataRegion = new ExcelDataRegion();
            dataRegion.WorksheetName = this.txt_SheetName.Text;
            dataRegion.Range = this.txt_Range.Text;
            dataRegion.Sql = this.rt_SQL.Text;
            dataRegion.FillAction = (ExcelDataFillAction)this.cbo_Actions.SelectedIndex;
            dataRegion.KeyPosition = this.txt_KeyColIndex.Text;
            dataRegion.KeyName = this.txt_KeyColumnName.Text;
            dataRegion.ExcelColumnBindings = new Dictionary<string, string>();

            if (dataRegion.FillAction != ExcelDataFillAction.FillValue && dataRegion.Range.IndexOf(":") > 0)
            {
                List<string> srcColNames = new List<string>();
                List<string> excelColNames = new List<string>();
                string sql = dataRegion.Sql;
                string selection = sql.Substring(sql.ToLower().IndexOf("select") + "select ".Length);
                selection = selection.Substring(0, selection.ToLower().IndexOf("from"));
                string[] selectedColumns = selection.Split(new char[] { ',' });
                foreach (string selectedCol in selectedColumns)
                {
                    string dataColName = selectedCol;
                    if (selectedCol.ToLower().IndexOf(" as ") > 0)
                    {
                        dataColName = selectedCol.Substring(selectedCol.ToLower().IndexOf(" as ") + " as ".Length);
                    }
                    dataColName = dataColName.Trim();
                    if (dataColName.IndexOf(".") > 0)
                    {
                        dataColName = dataColName.Substring(dataColName.IndexOf(".") + 1);
                    }
                    if (dataColName.IndexOf(" ") > 0)
                    {
                        dataColName = "Col_" + (srcColNames.Count + 1);
                    }
                    srcColNames.Add(dataColName);
                }
                string leftCellAddr = dataRegion.Range.Substring(0, dataRegion.Range.IndexOf(":"));
                leftCellAddr = leftCellAddr.Replace("$", "");
                int leftColIndex = WorksheetHelper.GetColumnIndex(leftCellAddr);
                string rightCellAddr = dataRegion.Range.Substring(dataRegion.Range.IndexOf(":") + 1);
                rightCellAddr = rightCellAddr.Replace("$", "");
                int rightColIndex = WorksheetHelper.GetColumnIndex(rightCellAddr);
                for (int i = leftColIndex; i <= rightColIndex; i++)
                {
                    string excelColName = WorksheetHelper.GetColumnName(i);
                    if (!string.IsNullOrEmpty(excelColName))
                    {
                        excelColNames.Add(excelColName);
                    }
                }
                ExcelColumnBinding excelColBindingDesigner = new ExcelColumnBinding();
                excelColBindingDesigner.Show(this.ParentForm);
                excelColBindingDesigner.SetColumns(dataRegion, srcColNames, excelColNames);
                excelColBindingDesigner.OnColumnBindingsChanged += new ColumnBindingsChanged(excelColBindingDesigner_OnColumnBindingsChanged);
            }
            else
            {
                this.AddDataRegion(dataRegion);
            }
        }

        private void AddDataRegion(ExcelDataRegion dataRegion)
        {
            bool exist = false;
            foreach (DataGridViewRow dr in this.dv_Regions.Rows)
            {
                if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[1].Value != null)
                {
                    string worksheetName = dr.Cells[0].Value.ToString();
                    string rangeName = dr.Cells[1].Value.ToString();
                    if (worksheetName == dataRegion.WorksheetName && rangeName == dataRegion.Range)
                    {
                        exist = true;

                        DataGridViewTextBoxCell cellSQL2 = (DataGridViewTextBoxCell)dr.Cells[2];
                        cellSQL2.Value = dataRegion.Sql;
                        if (dataRegion.FillAction == ExcelDataFillAction.FillTableByKey)
                        {
                            DataGridViewComboBoxCell cellAction2 = (DataGridViewComboBoxCell)dr.Cells[5];
                            cellAction2.Value = dataRegion.FillAction.ToString();

                            DataGridViewTextBoxCell cellKeyPos2 = (DataGridViewTextBoxCell)dr.Cells[3];
                            cellKeyPos2.Value = dataRegion.KeyPosition;
                            DataGridViewTextBoxCell cellKeyName2 = (DataGridViewTextBoxCell)dr.Cells[4];
                            cellKeyName2.Value = dataRegion.KeyName;

                            DataGridViewTextBoxCell cellBindings = (DataGridViewTextBoxCell)dr.Cells[6];
                            string bindings = string.Empty;
                            if (dataRegion.ExcelColumnBindings != null && dataRegion.ExcelColumnBindings.Count > 0)
                            {
                                foreach (string key in dataRegion.ExcelColumnBindings.Keys)
                                {
                                    if (bindings.Length > 0)
                                    {
                                        bindings += ",";
                                    }
                                    bindings += key + "=" + dataRegion.ExcelColumnBindings[key];
                                }
                            }
                            cellBindings.Value = bindings;
                        }
                    }
                }
            }
            if (!exist)
            {
                DataGridViewRow dr = new DataGridViewRow();
                DataGridViewTextBoxCell cellSheet = new DataGridViewTextBoxCell();
                cellSheet.Value = dataRegion.WorksheetName;
                dr.Cells.Add(cellSheet);
                DataGridViewTextBoxCell cellRange = new DataGridViewTextBoxCell();
                cellRange.Value = dataRegion.Range;
                dr.Cells.Add(cellRange);
                DataGridViewTextBoxCell cellSql = new DataGridViewTextBoxCell();
                cellSql.Value = dataRegion.Sql;
                dr.Cells.Add(cellSql);
                DataGridViewTextBoxCell cellPos = new DataGridViewTextBoxCell();
                cellPos.Value = dataRegion.KeyPosition;
                dr.Cells.Add(cellPos);
                DataGridViewTextBoxCell cellKeyName = new DataGridViewTextBoxCell();
                cellKeyName.Value = dataRegion.KeyName;
                dr.Cells.Add(cellKeyName);
                DataGridViewComboBoxCell cellAction = new DataGridViewComboBoxCell();
                for (int i = 0; i <= 2; i++)
                {
                    ExcelDataFillAction action = (ExcelDataFillAction)i;
                    cellAction.Items.Add(action.ToString());
                }
                cellAction.Value = dataRegion.FillAction.ToString();
                dr.Cells.Add(cellAction);

                DataGridViewTextBoxCell cellBindings = new DataGridViewTextBoxCell();
                string bindings = string.Empty;
                if (dataRegion.ExcelColumnBindings != null && dataRegion.ExcelColumnBindings.Count > 0)
                {
                    foreach (string key in dataRegion.ExcelColumnBindings.Keys)
                    {
                        if (bindings.Length > 0)
                        {
                            bindings += ",";
                        }
                        bindings += key + "=" + dataRegion.ExcelColumnBindings[key];
                    }
                }
                cellBindings.Value = bindings;
                dr.Cells.Add(cellBindings);

                this.dv_Regions.Rows.Add(dr);
            }
        }

        void excelColBindingDesigner_OnColumnBindingsChanged(ExcelDataRegion dataRegion, Dictionary<string, string> bindings)
        {
            dataRegion.ExcelColumnBindings = bindings;
            this.AddDataRegion(dataRegion);
        }

        private void btn_OK_Click(object sender, EventArgs e)
        {
            this.SaveSettings();
            this._ExcelReport.Instruction = this._Instruction;
            if (this.UpdateComponentSettingEvent != null)
            {
                this.UpdateComponentSettingEvent(this._OwnerActivityNode, this._ExcelReport);
            }

            this.Close();
        }

        private void btn_Cancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_Browse_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Excel reports(*.xls;*.xlsx)|*.xls;*.xlsx|All files(*.*)|*.*";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.txt_ReportFilePath.Text = dlg.FileName;
            }
        }

        private void axSpreadsheet1_SelectionChange(object sender, EventArgs e)
        {
            string worksheetName = this.axSpreadsheet1.ActiveSheet.Name;
            this.txt_SheetName.Text = worksheetName;
            object missing = Missing.Value;
            string range = this.axSpreadsheet1.Selection.get_Address(ref missing, ref missing, XlReferenceStyle.xlA1, ref missing, ref missing);
            this.txt_Range.Text = range;
            Regex addrRegex = new Regex("^([^\\d]+)(\\d+):?([^\\d]+)(\\d+)$", RegexOptions.IgnoreCase);
            Match addrMatch = addrRegex.Match(range.Replace("$", ""));
            if (addrMatch.Success)
            {
                this.txt_KeyColIndex.Text = addrMatch.Groups[1].Value;
            }
            else
            {
                this.txt_KeyColIndex.Text = range;
            }
            this.rt_SQL.Text = string.Empty;
            if (this._Instruction.DataRegions != null && this._Instruction.DataRegions.Count > 0)
            {
                foreach (ExcelDataRegion region in this._Instruction.DataRegions)
                {
                    if (region.WorksheetName == worksheetName && region.Range == range)
                    {
                        this.rt_SQL.Text = region.Sql;
                        this.cbo_Actions.SelectedIndex = (int)region.FillAction;
                        this.txt_KeyColIndex.Text = region.KeyPosition;
                        this.txt_KeyColumnName.Text = region.KeyName;
                        break;
                    }
                }
            }
        }


    }
}
